package com.laozhao.msas.util;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.DateFormulaCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.StringFormulaCell;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelDataUtil {

	public static void saveToExcel(
			Map<String, Object[][]> data, String dataFilePath,String sheetPassword) {
		saveToExcel(data,  new File(dataFilePath), sheetPassword);
	}
	

	public static void saveToExcel(
			Map<String,Object[][]> data,File dataFile,String sheetPassword) {

		try {
			Workbook rwb = Workbook.getWorkbook(dataFile.getAbsoluteFile());
			WritableWorkbook dataExcelWorkBook = Workbook.createWorkbook(dataFile.getAbsoluteFile(), rwb);// copy
			int sheetIndex = 0;
			WritableSheet sheet;
			Object[][] sheetData;
			for (String sheetName : data.keySet()) {
				sheet = dataExcelWorkBook.createSheet(sheetName, sheetIndex++ );
				
				sheetData = data.get(sheet);
				for (int row = 0; row < sheetData.length; row++) {
					for (int column = 0; column < sheetData[row].length; column++) {
						sheet.addCell(parseCell(column, row, sheetData[row][column]));
					}
				}
				if (sheetPassword != null) {
					SheetSettings set = new SheetSettings(sheet);
					set.setPassword(sheetPassword);
					set.setProtected(true);
				}
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	public static Map<String,Object[][]> getExcelData(
			String dataFilePath,String sheetPassword) {
		return getExcelData(new File(dataFilePath),sheetPassword);
	}
	

	public static Map<String,Object[][]> getExcelData(
			File dataFile,String sheetPassword) {
		Map<String,Object[][]> reMap = null;
		try {
			// read file
			Workbook workbook = Workbook.getWorkbook(dataFile.getAbsoluteFile());
			int sheetCounts =  workbook.getSheets().length;
			reMap = new HashMap<String, Object[][]>(sheetCounts);
			//read sheet
			Sheet sheet;
			Object[][] data;
			for (int i = 0; i < sheetCounts; i++) {
				sheet = workbook.getSheet(i);
				// check protected
				if (sheetPassword != null) {
					SheetSettings set = sheet.getSettings() ;
					set.setPassword(sheetPassword);
					set.setProtected(false);
				}
				data = new Object[sheet.getRows()][sheet.getColumns()];
				for (int row = 0; row < sheet.getRows(); row++) {
					for (int column = 0; column < sheet.getColumns(); column++) {
						data[row][column] = parseObject(sheet.getCell(column, row));
					}
				}
				reMap.put(sheet.getName(), data);
			}			
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}		
		return reMap;
	}

	private static WritableCell parseCell(int column, int row, Object value) {
		WritableCell cell = null;
		if (value instanceof String) {
			cell = new Label(column, row, (String) value);
		} else if (value instanceof Date) {
			cell = new DateTime(column, row, (Date) value);
			System.out.println(cell.getContents());
		} else if (value instanceof Number) {
			cell = new jxl.write.Number(column, row, ((Number) value)
					.doubleValue());
		} else if (value instanceof Boolean) {
			cell = new jxl.write.Boolean(0, 2, (Boolean) value);
		} else {
			cell = new Label(column, row, value == null ? "" : value.toString());
		}
		return cell;
	}

	private static Object parseObject(Cell cell) {
		Object value = null;
		try {
			if (cell.getType() == CellType.LABEL) {
				value = ((LabelCell) cell).getString();
			} else if (cell.getType() == CellType.STRING_FORMULA) {
				value = ((StringFormulaCell) cell).getString();
			} else if (cell.getType() == CellType.DATE) {
				value = ((DateCell) cell).getDate();
			} else if (cell.getType() == CellType.DATE_FORMULA) {
				value = ((DateFormulaCell) cell).getDate();
			} else if (cell.getType() == CellType.NUMBER) {
				value = ((NumberCell) cell).getValue();
			} else if (cell.getType() == CellType.NUMBER_FORMULA) {
				value = ((jxl.NumberFormulaCell) cell).getValue();
			} else if (cell.getType() == CellType.BOOLEAN) {
				value = ((BooleanCell) cell).getValue();
			} else if (cell.getType() == CellType.BOOLEAN_FORMULA) {
				value = ((jxl.BooleanFormulaCell) cell).getValue();
			} else {
				value = cell.getContents();
			}
		} catch (Exception e) {
			e.printStackTrace();
			value = cell.getContents();
		}
		return value;
	}
}
